﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using HNQXYJ.Model;

namespace HNQXYJ.DAL
{
    public class CountyDAL
    {
       /// <summary>
       /// 将一行县的信息转化为一个数据对象模型
       /// </summary>
       /// <param name="dr"></param>
       /// <returns></returns>
        public HCounty ToModel(DataRow dr)
        {
            HCounty cty = new HCounty();
            cty.ID = Convert.ToInt32(dr["id"]);
            cty.Name = dr["Name"].ToString();
            cty.ParentCode = dr["ParentCode"].ToString();
            cty.SelfCode = dr["SelfCode"].ToString();
            cty.DateAdd = Convert.ToDateTime(dr["DateAdd"]);
            cty.Lng = Convert.ToDouble(dr["Lng"]);
            cty.Lat = Convert.ToDouble(dr["Lat"]);
            return cty;
        }


        /// <summary>
        /// 通过城市的代码，获取该城市的所有县
        /// </summary>
        /// <param name="parentcode"></param>
        /// <returns></returns>
        public List<HCounty> GetCountyListByParentCode(string parentcode)
        {
            string procName = "CountyGetDataByParentCode";
            SqlParameter[] paras = { 
                                   new SqlParameter("@ParentCode",SqlDbType.VarChar,100)
                                   };
            paras[0].Value = parentcode;
            List<HCounty> list = new List<HCounty>();
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            foreach (DataRow row in dt.Rows)
            {
                list.Add(ToModel(row));
            }
            return list;
        }

        /// <summary>
        /// 增加新的县（区）
        /// </summary>
        /// <param name="county"></param>
        /// <returns></returns>
        public int AddCounty(HCounty county)
        {
            string procName = "CountyInsertData";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Name",SqlDbType.NVarChar,50),
                                   new SqlParameter("@ParentCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@SelfCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@DateAdd",SqlDbType.DateTime),
                                   new SqlParameter("@Lng",SqlDbType.Float),
                                   new SqlParameter("@Lat",SqlDbType.Float)
                                   };
            paras[0].Value = county.Name;
            paras[1].Value = county.ParentCode;
            paras[2].Value = county.SelfCode;
            paras[3].Value = county.DateAdd;
            paras[4].Value = county.Lng;
            paras[5].Value = county.Lat;

            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }

        /// <summary>
        /// 根据一个id删除一个县
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteCountyById(int id)
        {
            string procName = "CountyDeleteById";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int)
                                   };
            paras[0].Value = id;
            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }

        

        /// <summary>
        /// 根据id获取县区对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public HCounty GetCountyById( int id)
        {
            HCounty county = new HCounty();
            string procName = "CountyGetDataById";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int)
                                   };
            paras[0].Value = id;
            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            if (dt.Rows.Count != 0)
            {
                DataRow dr = dt.Rows[0];
                county = ToModel(dr);
                return county;
            }
            else
            {
                return null;
            }

        }

        /// <summary>
        /// 更新一个县区对象
        /// </summary>
        /// <param name="county"></param>
        /// <returns></returns>
        public int UpdateCounty(HCounty county)
        {
            string procName = "CountyUpdateData";
            SqlParameter[] paras = { 
                                   new SqlParameter("@Id",SqlDbType.Int),
                                   new SqlParameter("@Name",SqlDbType.NVarChar,50),
                                   new SqlParameter("@ParentCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@SelfCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@Lng",SqlDbType.Float),
                                   new SqlParameter("Lat",SqlDbType.Float)
                                   };
            paras[0].Value = county.ID;
            paras[1].Value = county.Name;
            paras[2].Value = county.ParentCode;
            paras[3].Value = county.SelfCode;
            paras[4].Value = county.Lng;
            paras[5].Value = county.Lat;

            int res = SqlHelper.ExecNonQuery(procName, paras);
            return res;
        }

        /// <summary>
        /// 对所有的县区进行分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="parentCode"></param>
        /// <param name="rowCount"></param>
        /// <param name="pageCount"></param>
        /// <returns></returns>
        public List<HCounty> GetCountyPagedList(int pageIndex, int pageSize, string parentCode, out int rowCount, out int pageCount)
        {
            rowCount = 0;
            pageCount = 0;
            string procName = "CountyPaging";
            SqlParameter[] paras = { 
                                   new SqlParameter("@PageIndex",SqlDbType.Int),
                                   new SqlParameter("@PageSize",SqlDbType.Int),
                                   new SqlParameter("@ParentCode",SqlDbType.VarChar,100),
                                   new SqlParameter("@RowCount",SqlDbType.Int),
                                   new SqlParameter("@PageCount",SqlDbType.Int)
                                   };
            paras[0].Value = pageIndex;
            paras[1].Value = pageSize;
            paras[2].Value = parentCode;
            paras[3].Value = rowCount;
            paras[3].Direction = ParameterDirection.Output;
            paras[4].Value = pageCount;
            paras[4].Direction = ParameterDirection.Output;

            DataTable dt = SqlHelper.GetDataTable(procName, paras);
            if (dt.Rows.Count > 0)
            {
                List<HCounty> list = new List<HCounty>();
                foreach (DataRow row in dt.Rows)
                {
                    list.Add(ToModel(row));
                }
                rowCount = Convert.ToInt32(paras[3].Value);
                pageCount = Convert.ToInt32(paras[4].Value);
                return list;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据一个县的代码获取该县的对象
        /// </summary>
        /// <param name="selfCode"></param>
        /// <returns></returns>
        public HCounty GetCountyBySelfCode(string selfCode)
        {
            string procName = "CountyGetDataBySelfCode";
            SqlParameter[] paras ={
                                     new SqlParameter("@Selfcode",SqlDbType.VarChar,100)
                                 };
            paras[0].Value = selfCode;
            DataTable dt=SqlHelper.GetDataTable(procName ,paras);
            if(dt.Rows.Count>0)
            {
                HCounty county=new HCounty();
                county=ToModel(dt.Rows[0]);
                return county;
            }
            else
            {
                return null;
            }
        }
    }
}